<?php
/**
 * Note: 由指定模型引入, 通过 ide-helper:models 生成索引文件后, 能正确解析
 * Date: 2019/7/20
 * Time: 1:06
 */

namespace App\Traits;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Grammars\Grammar;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;

trait ModelTrait
{
    /**
     * 本地作用域 like查询
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfLike(Builder $query, $field = '', $data = '')
    {
        if (!$data) return $query;
        return $query->where($field, 'LIKE', "%$data%");
    }

    /**
     * 本地作用域 like LEFT查询 左前匹配 可利用索引
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfLikeLeft(Builder $query, $field = '', $data = '')
    {
        if (!$data) return $query;
        return $query->where($field, 'LIKE', "$data%");
    }

    /**
     * 本地作用域 参数字段
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfField(Builder $query, $field = '', $data = '')
    {
        if (!$data) return $query;
        return is_array($data) ? $query->whereIn($field, $data) : $query->where($field, $data);
    }

    /**
     * 本地作用域 IP2LONG
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfIp2long(Builder $query, $field = '', $data = '')
    {
        if (!$data) return $query;
        return is_array($data) ? $query->whereIn($field, array_map('ip2long', $data)) : $query->where($field, ip2long($data));
    }

    /**
     * 指定字段作用域
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfFieldNumeric(Builder $query, string $field, $data = ''): Builder
    {
        if (!is_array($data) && !is_numeric($data)) return $query;
        if (is_array($data) && !$data) return $query;

        // mongodb 需要严格字段匹配 这里强制转为 float
        if ($this instanceof \Jenssegers\Mongodb\Eloquent\Model) {
            $data = is_array($data) ? array_map('floatval', $data) : floatval($data);
        }

        return is_array($data) ? $query->whereIn($field, $data) : $query->where($field, $data);
    }

    /**
     * boolean值
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfFieldBoolean(Builder $query, string $field, $data = '')
    {
        if (!is_bool($data)) {
            if ($data === 'true') $data = true;
            else if ($data === 'false') $data = false;
        }
        if (!is_bool($data)) return $query;

        return $query->where($field, $data);
    }

    /**
     * 本地作用域 指定时间戳字段
     * @param Builder $query
     * @param $timeField
     * @param $timeRange
     * @return Builder
     */
    public function scopeOfTimestampRange(Builder $query, $timeField, $timeRange)
    {
        if (!$timeField || !$timeRange) return $query;

        // mongodb 需要严格字段匹配 这里强制转为 float
        if ($this instanceof \Jenssegers\Mongodb\Eloquent\Model) {
            $timeRange = array_map('floatval', $timeRange);
        }

        return $query->whereBetween($timeField, $timeRange);
    }

    /**
     * 本地作用域 指定 date 时间段
     * @param Builder $query
     * @param $timeField
     * @param $timeRange
     * @return Builder
     */
    public function scopeOfDateRange(Builder $query, $timeField, $timeRange)
    {
        if (!$timeField || !$timeRange) return $query;
        if (is_numeric($timeRange[0])) $timeRange[0] = Carbon::createFromTimestamp($timeRange[0])->toDateString();
        if (is_numeric($timeRange[1])) $timeRange[1] = Carbon::createFromTimestamp($timeRange[1])->toDateString();
        return $query->whereBetween($timeField, $timeRange);
    }

    /**
     * 本地作用域 指定 datetime 时间段
     * @param Builder $query
     * @param $timeField
     * @param $timeRange
     * @return Builder
     */
    public function scopeOfDatetimeRange(Builder $query, $timeField, $timeRange): Builder
    {
        if (!$timeField || !$timeRange) return $query;

        // mongo 直接使用 carbon 模型, mysql 则使用 string
        $mongo = $this instanceof \Jenssegers\Mongodb\Eloquent\Model;
        $timeRange = array_map(function ($time) use ($mongo) {
            if (is_numeric($time)) {
                $time = Carbon::createFromTimestamp($time);

                if (!$mongo) $time = $time->toDateTimeString();
            } else if (is_string($time)) {
                if ($mongo) $time = Carbon::parse($time);
            }
            return $time;
        }, $timeRange);

        return $query->whereBetween($timeField, $timeRange);
    }

    /**
     * 多个值 (可能是逗号隔开)
     * @param Builder $query
     * @param $field
     * @param $data
     * @return Builder
     */
    public function scopeOfMultiValue(Builder $query, string $field, $data)
    {
        if (!$data) return $query;
        if (!is_array($data) && strpos($data, ',') !== false) {
            $data = explode(',', $data);
            $data = array_unique($data);
        }
        return is_array($data) ? $query->whereIn($field, $data) : $query->where($field, $data);
    }

    /**
     * 自定义排序
     * todo 有注入风险, 仅供后台查询使用
     * @param Builder $query
     * @param string $field
     * @param string $value
     * @return Builder
     */
    public function scopeOfOrder(Builder $query, $field = '', $value = '')
    {
        if (!$field || !$value) return $query;
        if (strpos($field, '+') || strpos($field, '-')) $field = DB::raw($field);
        return $query->orderBy($field, $value);
    }

    /**
     * 本地作用域 按位与
     * @param Builder $query
     * @param string $field
     * @param string $data
     * @return Builder
     */
    public function scopeOfBit(Builder $query, $field = '', $data = '')
    {
        if (!$data) return $query;
        if (is_array($data)) {
            $data = array_sum($data);
        }
        return $query->where($field, '>', 0)->whereRaw("({$field} & {$data}) = {$data}");
    }

    /**
     * 本地作用域 查找json字段是否包含数字
     * @param Builder $query
     * @param string $field
     * @param null $data
     * @return Builder
     */
    public function scopeOfJsonContainsNumeric(Builder $query, string $field = '', $data = null)
    {
        if (!is_array($data) && !is_numeric($data)) return $query;
        if (is_numeric($data)) return $query->whereJsonContains($field, floatval($data));
        return $query->where(function(Builder $query) use ($field, $data) {
            foreach ($data as $v) {
                $query->whereJsonContains($field, floatval($v));
            }
        });
    }

    /**
     * 本地作用域 查找json字段是否包含数字(满足任意一个)
     * @param Builder $query
     * @param string $field
     * @param null $data
     * @return Builder
     */
    public function scopeOfJsonContainsNumericAny(Builder $query, string $field = '', $data = null)
    {
        if (!is_array($data) && !is_numeric($data)) return $query;
        if (is_numeric($data)) return $query->whereJsonContains($field, floatval($data));
        return $query->where(function(Builder $query) use ($field, $data) {
            foreach ($data as $v) {
                $query->orWhereJsonContains($field, floatval($v));
            }
        });
    }

    /**
     * 重写 firstOrCreate, 读操作从写库读
     * @param array $attributes
     * @param array $values
     * @return \Illuminate\Support\HigherOrderTapProxy|mixed
     */
    public static function firstOrCreate(array $attributes, array $values = [])
    {
        if (!is_null($instance = self::onWriteConnection()->where($attributes)->first())) {
            return $instance;
        }
        // self::create($attributes + $values);
        return tap(self::newModelInstance($attributes + $values), function ($instance) {
            $instance->save();
        });
    }

    /**
     * 批量更新 根据主键
     * @param array $multipleData
     * @return bool|\Illuminate\Database\Query\Builder
     */
    public static function updateBatch(array $multipleData = [])
    {
        $instance = new self();
        if (empty($multipleData)) return true;

        $fullTableName = $instance->getConnection()->getTablePrefix() . $instance->getTable();
        $primaryKey = $instance->getKeyName();

        // column or fields to update
        $updateColumn = array_keys($multipleData[array_key_first($multipleData)]);
        $whereIn = "";

        $sql = "UPDATE `{$fullTableName}` SET ";
        foreach ($updateColumn as $uColumn) {
            if ($uColumn === $primaryKey) continue;
            if (!in_array($uColumn, $instance->fillable)) continue;
            $sql .= $uColumn . " = (CASE ";

            foreach ($multipleData as $data) {
                $sql .= "WHEN " . $primaryKey . " = " . $data[$primaryKey] . " THEN '" . $data[$uColumn] . "' ";
            }
            $sql .= "ELSE " . $uColumn . " END), ";
        }
        foreach ($multipleData as $data) {
            $whereIn .= "'" . $data[$primaryKey] . "', ";
        }
        $sql = rtrim($sql, ", ") . " WHERE " . $primaryKey . " IN (" . rtrim($whereIn, ', ') . ")";

        // Update
        return DB::connection($instance->getConnectionName())->update(DB::raw($sql));
    }

    /**
     * 获取表全名
     * @return string
     */
    public static function getFullTable(): string
    {
        $model = self::getModel();
        return config('database.connections.'.$model->getConnectionName().'.prefix').$model->getTable();
    }

    /**
     * insert on duplicate key update
     * 批量插入或更新
     * TODO 表必须有主键或唯一索引
     * @param array $values 待插入数据, 支持批量
     * @param array $value 待更新数据, 使用插入的字段值可写 ['score' => DB::raw('`values(score)`')]
     * @return bool
     */
    public static function insertOrDuplicateUpdate(array $values, array $value): bool
    {
        // 每条数据key排序要一致
        $values = array_map(function($v) {
            ksort($v);
            return $v;
        }, $values);

        $instance = new self();
        $connection = $instance->getConnection();   // 数据库连接
        $builder = $instance->newQuery()->getQuery();   // 查询构造器
        $grammar = $builder->getGrammar();  // 语法器
        // 编译插入语句
        $insert = $grammar->compileInsert($builder, $values);
        // 编译重复后更新列语句。
        $update = $instance->compileUpdateColumns($grammar, $value);
        // 构造查询语句
        $query = $insert.' on duplicate key update '.$update;
        // 组装sql绑定参数
        $bindings = $instance->prepareBindingsForInsertOrUpdate($values, $value);
        // 执行数据库查询
        return $connection->insert($query, $bindings);
    }

    /**
     * Compile all of the columns for an update statement.
     *
     * @param Grammar $grammar
     * @param array $values
     * @return string
     */
    private function compileUpdateColumns(Grammar $grammar, array $values): string
    {
        return collect($values)->map(function ($value, $key) use ($grammar) {
            return $grammar->wrap($key).' = '.$grammar->parameter($value);
        })->implode(', ');
    }

    /**
     * Prepare the bindings for an insert or update statement.
     *
     * @param array $values
     * @param array $value
     * @return array
     */
    private function prepareBindingsForInsertOrUpdate(array $values, array $value): array
    {
        // Merge array of bindings
        $bindings = array_merge_recursive($values, [$value]);

        // Remove all of the expressions from a list of bindings.
        return array_values(array_filter(Arr::flatten($bindings, 1), function ($binding) {
            return ! $binding instanceof \Illuminate\Database\Query\Expression;
        }));
    }
}
